package net.comm.group.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class GpartyServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from Gparty" +
				" where partyID=?";
	}

	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert  into Gparty" +
				" (  partyID,  groupID,  title,  info, smallPic,  bgtime,  endtime,  work_location," +
				" work_sublocation,  firsort,  secsort,  thisort,place,  tel,  signEtime,  charge," +
				" planNum,  signNum,  organType,  userID, ctime,  attach, upCnt, downCnt,  status,  mtime  )" +
				" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update Gparty" +
				"  set groupID=?,  title=?,  info=?,  smallPic=?,  bgtime=?,  endtime=?," +
				"  work_location=?, work_sublocation=?,  firsort=?,  secsort=?,  thisort=?,place=?, " +
				" tel=?,  signEtime=?, charge=?,  planNum=?,  signNum=?," +
				"  organType=?,  userID=?, ctime=?,  attach=?, upCnt=?, downCnt=?,  status=?,  mtime=? " +
				"  where partyID=? ";
	}

	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdnumSql()
	{
		return "update Gparty" +
				"  set  signNum=?, attach=?,  upCnt=?, downCnt=?, status=? " +
				"  where partyID=? ";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select partyID,  groupID,  title,  info, smallPic,  bgtime,  endtime,  work_location," +
				" work_sublocation,  firsort,  secsort,  thisort,place,  tel,  signEtime,  charge," +
				" planNum,  signNum,  organType,  userID, ctime,  attach, upCnt, downCnt,  status,  mtime " +
				" from Gparty" +
				" where partyID=?";
	}
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakaiuseridmap table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select partyID,  groupID,  title,  info, smallPic,  bgtime,  endtime,  work_location," +
				" work_sublocation,  firsort,  secsort,  thisort,place,  tel,  signEtime,  charge," +
				" planNum,  signNum,  organType,  userID, ctime,  attach,  upCnt, downCnt, status,  mtime " +
				" from Gparty");
		
		sb.append(" where status=? Order by mtime desc ");
		sb.append(" limit ?, ?");
				
		return sb.toString();	
	}	

	/**
	 * return the sql statement which updates an external user id for a given user in the sakaiuseridmap table.
	 */
	public String getRecMyaddSql()
	{
	
		StringBuffer sb =  new StringBuffer("select p.partyID,  p.groupID,  p.title,  p.info, p.smallPic," +
				"  p.bgtime,  p.endtime,  p.work_location, p.work_sublocation,  p.firsort,  p.secsort,  " +
				"p.thisort, p.place,  p.tel,  p.signEtime, p.charge, p.planNum,  p.signNum,  p.organType," +
				" p.userID, p.ctime,  p.attach,  p.upCnt, p.downCnt, p.status,  p.mtime " +
				" from Gparty p left join Gsignup s on p.PartyID = s.PartyID");
		
		sb.append(" where p.GroupID=? and s.userID=? Group by p.PartyID Order by p.mtime desc ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	

	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakaiuseridmap table.
	 */
	public String getRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select partyID,  groupID,  title,  info, smallPic,  bgtime,  endtime,  work_location," +
				" work_sublocation,  firsort,  secsort,  thisort,place,  tel,  signEtime,  charge," +
				" planNum,  signNum,  organType,  userID, ctime,  attach,  upCnt, downCnt, status,  mtime " +
				" from Gparty");
		return sb.toString();	
	}	
	
	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from Gparty";
	}

	/**
	 * 返回根据查询条件的记录数 
	 */
	public String getCountSql2()
	{
		return " select count(*) as COUNT from Gparty p left join Gsignup s on p.PartyID = s.PartyID" +
				" where p.GroupID=? and s.userID=? Group by p.PartyID ";
	}

	/**
	 * @see net.comm.job.db.iServiceSql.impl.AnswererviceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
}
